/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Controller;

import Model.login;
import Controller.LogManager;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.Timestamp;
import java.util.Date;
import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

/**
 *
 * @author Martin Spencer Sy
 */
public class UserManager {
    private static Connection con = null;
    private static String sql = null;
    private static ResultSet rs = null;
    public static String name = null;
    public static int user = -1,
            access = -1;
    
    public static ResultSet getUserInfo(String user){
        PreparedStatement ps;
        
        try{
            
            Class.forName("com.mysql.jdbc.Driver");
            
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/foobar", 
                    "root", "");
            
            sql = "SELECT * FROM listUserInfo WHERE id = ?";
            
            ps = con.prepareStatement(sql);
            
            ps.setString(1, user);
            
            rs = ps.executeQuery();
            
        }
        catch (SQLException ex) {
            //Logger.getLogger(login.class.getName()).log(Level.SEVERE, null, ex);            
            LogManager.logEvent(UserManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught SQL Exception: " + ex.getMessage(), null, user);
        }
        catch (ClassNotFoundException e1) {
            // JDBC driver class not found, print error message to the console
            System.out.println(e1.toString());
            LogManager.logEvent(UserManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Class Not Found Exception: " + e1.getMessage(), null, user);
        }
        catch (Exception e3) {
            // other unexpected exception, print error message to the console
            System.out.println(e3.toString());
            LogManager.logEvent(UserManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Exception: " + e3.getMessage(), null, user);
        }
        
        return rs;
    }
    
    public static ResultSet getUserBought(String user){
        PreparedStatement ps;
        
        try{
            
            Class.forName("com.mysql.jdbc.Driver");
            
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/foobar", 
                    "root", "");
            
            sql = "SELECT * FROM listUserBought WHERE uid = ?";
            
            ps = con.prepareStatement(sql);
            
            ps.setString(1, user);
            
            rs = ps.executeQuery();
            
        }
        catch (SQLException ex) {
            //Logger.getLogger(login.class.getName()).log(Level.SEVERE, null, ex);            
            LogManager.logEvent(UserManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught SQL Exception: " + ex.getMessage(), null, user);
        }
        catch (ClassNotFoundException e1) {
            // JDBC driver class not found, print error message to the console
            System.out.println(e1.toString());
            LogManager.logEvent(UserManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Class Not Found Exception: " + e1.getMessage(), null, user);
        }
        catch (Exception e3) {
            // other unexpected exception, print error message to the console
            System.out.println(e3.toString());
            LogManager.logEvent(UserManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Exception: " + e3.getMessage(), null, user);
        }
        
        return rs;
    }
    
    public static boolean isBought(String user, String product){
        boolean result;
        PreparedStatement ps;
        
        try{
            Class.forName("com.mysql.jdbc.Driver");
            
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/foobar", 
                    "root", "");
            
            sql = "SELECT * FROM listUserBought WHERE uid = ? AND pid = (SELECT pid FROM listProduct WHERE pindex = ?)";
            
            ps = con.prepareStatement(sql);
            
            ps.setInt(1, Integer.parseInt( (String)user ));
            ps.setString(2, product);
            
            rs = ps.executeQuery();
            
            result = rs.next();
            
            return result;
            
        }
        catch (SQLException ex) {
            //Logger.getLogger(login.class.getName()).log(Level.SEVERE, null, ex);            
            LogManager.logEvent(UserManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught SQL Exception: " + ex.getMessage(), null, user);
        }
        catch (ClassNotFoundException e1) {
            // JDBC driver class not found, print error message to the console
            System.out.println(e1.toString());
            LogManager.logEvent(UserManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Class Not Found Exception: " + e1.getMessage(), null, user);
        }
        catch (Exception e3) {
            // other unexpected exception, print error message to the console
            System.out.println(e3.toString());
            LogManager.logEvent(UserManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Exception: " + e3.getMessage(), null, user);
        }
        
        return false;
    }
    
    public static int isAdmin(String user){
        int result = 0;
        PreparedStatement ps;
        
        try{
            
            Class.forName("com.mysql.jdbc.Driver");
            
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/foobar", 
                    "root", "");
            
            sql = "SELECT * FROM listUserAccess WHERE uid = ?";
            
            ps = con.prepareStatement(sql);
            
            ps.setString(1, user);
            
            rs = ps.executeQuery();
            
            while( rs.next() ){
                result = rs.getInt(2);
            }
            
            return result;
            
        }
        catch (SQLException ex) {
            //Logger.getLogger(login.class.getName()).log(Level.SEVERE, null, ex);            
            LogManager.logEvent(UserManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught SQL Exception: " + ex.getMessage(), null, user);
        }
        catch (ClassNotFoundException e1) {
            // JDBC driver class not found, print error message to the console
            System.out.println(e1.toString());
            LogManager.logEvent(UserManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Class Not Found Exception: " + e1.getMessage(), null, user);
        }
        catch (Exception e3) {
            // other unexpected exception, print error message to the console
            System.out.println(e3.toString());
            LogManager.logEvent(UserManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Exception: " + e3.getMessage(), null, user);
        }
        
        return result;
    }
    
    public static int isProductManager(String user){
        int result = 0;
        PreparedStatement ps;
        
        try{
            Class.forName("com.mysql.jdbc.Driver");
            
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/foobar", 
                    "root", "");
            
            sql = "SELECT * FROM listUserAccess WHERE uid = ?";
            
            ps = con.prepareStatement(sql);
            
            ps.setString(1, user);
            
            rs = ps.executeQuery();
            
            while( rs.next() ){
                result = rs.getInt(3);
            }
            
        }
        catch (SQLException ex) {
            //Logger.getLogger(login.class.getName()).log(Level.SEVERE, null, ex);
            LogManager.logEvent(UserManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught SQL Exception: " + ex.getMessage(), null, user);
        }
        catch (ClassNotFoundException e1) {
            // JDBC driver class not found, print error message to the console
            System.out.println(e1.toString());
            LogManager.logEvent(UserManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Class Not Found Exception: " + e1.getMessage(), null, user);
        }
        catch (Exception e3) {
            // other unexpected exception, print error message to the console
            System.out.println(e3.toString());
            LogManager.logEvent(UserManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Exception: " + e3.getMessage(), null, user);
        }
        
        return result;
    }
    
    public static int isFinanceManager(String user){
        int result = 0;
        PreparedStatement ps;
        
        try{
            Class.forName("com.mysql.jdbc.Driver");
            
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/foobar", 
                    "root", "");
            
            sql = "SELECT * FROM listUserAccess WHERE uid = ?";
            
            ps = con.prepareStatement(sql);
            
            ps.setString(1, user);
            
            rs = ps.executeQuery();
            
            while( rs.next() ){
                result = rs.getInt(4);
            }
            
            return result;
            
        }
        catch (SQLException ex) {
            //Logger.getLogger(login.class.getName()).log(Level.SEVERE, null, ex);
            LogManager.logEvent(UserManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught SQL Exception: " + ex.getMessage(), null, user);
        }
        catch (ClassNotFoundException e1) {
            // JDBC driver class not found, print error message to the console
            System.out.println(e1.toString());
            LogManager.logEvent(UserManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Class Not Found Exception: " + e1.getMessage(), null, user);
        }
        catch (Exception e3) {
            // other unexpected exception, print error message to the console
            System.out.println(e3.toString());
            LogManager.logEvent(UserManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Exception: " + e3.getMessage(), null, user);
        }
        
        return result;
    }
    
    //returns true if the session is valid and false otherwise
    public static boolean checkSessionIntegrity(String currSession, HttpSession session) throws NoSuchAlgorithmException, UnsupportedEncodingException{
        try{
            //hash the current session id and ip of the user
            MessageDigest md = MessageDigest.getInstance("SHA-256");
            md.update( currSession.getBytes("UTF-8") );
            byte[] raw = md.digest();
            md.reset();

            //convert the raw hash to hex
            StringBuilder sb = new StringBuilder();
            for (byte b : raw) {
                sb.append(String.format("%02X ", b));
            }
            String newDigest = sb.toString();
            
            if( newDigest.equals( session.getAttribute("sessionToken") ) ){
                return true;
            }
            else{
                return false;
            }
        }
        catch(NoSuchAlgorithmException e1){
            LogManager.logEvent(UserManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught No Such Algorithm Exception: " + e1.getMessage(), null, null);
            return false;
        }
        catch(UnsupportedEncodingException e1){
            LogManager.logEvent(UserManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Unsupported Encoding Exception: " + e1.getMessage(), null, null);
            return false;
        }
        catch(Exception e1){
            LogManager.logEvent(UserManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, e1.toString(), null, null);
            return false;
        }
    }
    
    public static int login(String username, String password) throws ServletException, IOException {
        PreparedStatement ps;
        Statement s;
        ResultSet userTime = null;
        
        try {
                Class.forName("com.mysql.jdbc.Driver");

                con = DriverManager.getConnection(
                        "jdbc:mysql://localhost:3306/foobar", 
                        "root", "");

                sql = "CALL loginUser(?, ?)";   //query to check if user and password is correct

                ps = con.prepareStatement(sql);

                ps.setString(1, username);
                ps.setString(2, password);

                //execute the instert statement
                rs = ps.executeQuery();

                if( rs.next() ){  //if the username and pw is correct
                    sql = "CALL getUserTime(?)";
                    ps = con.prepareStatement(sql);
                    ps.setInt( 1, rs.getInt("id") );
                    userTime = ps.executeQuery();
                    userTime.next();
                    
                    Date dateNow = new Date();
                    
                    Timestamp timeNow = new Timestamp( dateNow.getTime() ),
                            timeUser = userTime.getTimestamp(1);
                    
                    int tNow = (int) (timeNow.getTime()/(60*60*24*1000)),
                            tUser = (int) (timeUser.getTime()/(60*60*24*1000));
                    int tDiff = tNow-tUser;
                    
                    if( rs.getInt("status") < 5 && 
                            ( rs.getInt("isProductManager") == 0 && rs.getInt("isFinanceManager") == 0 ) ){//if normal user is not locked out
                        name = rs.getString("username");  //username of the logged in user
                        user = rs.getInt("id");  //user id of the logged in user
                        
                        //reset the status to 0
                        sql = "CALL unlockUserAcct('" + name + "')";
                        s = con.createStatement();
                        s.executeUpdate(sql);
                        
                        closeDB();
                        return 1;
                    }
                    else if( tDiff < 1 && 
                            ( rs.getInt("isProductManager") != 0 || rs.getInt("isFinanceManager") != 0 ) ){//if manager is not locked out
                        name = rs.getString("username");  //username of the logged in user
                        user = rs.getInt("id");  //user id of the logged in user
                        
                        //reset the status to 0
                        sql = "CALL unlockUserAcct('" + name + "')";
                        s = con.createStatement();
                        s.executeUpdate(sql);
                        
                        closeDB();
                        return 1;
                    }
                    else if( rs.getInt("status") >= 5 && 
                            ( rs.getInt("isProductManager") == 0 && rs.getInt("isFinanceManager") == 0 ) ){//if normal user is locked out
                        closeDB();
                        return 0;
                    }
                    else if( tDiff >= 1 && 
                            ( rs.getInt("isProductManager") != 0 || rs.getInt("isFinanceManager") != 0 ) ){//if manager is locked out
                        closeDB();
                        return 0;
                    }
                }
                else if( !rs.next() ){ //if username or password is incorrect
                    
                    sql = "SELECT username,status FROM user WHERE username = ?";
                    
                    ps = con.prepareStatement(sql);
                    
                    ps.setString(1, username);
                    
                    rs = ps.executeQuery();
                    
                    if( rs.next() ){//if user exist increment status until 5
                        if( rs.getInt("status") < 5 ){
                            sql = "CALL failedLogin('" + rs.getString("username") + "')";

                            s = con.createStatement();

                            s.executeUpdate(sql);
                            
                            Thread.sleep(3000);

                            return -1;
                        }
                        else if( rs.getInt("status") == 5 ){
                            return 0;
                        }
                    }
                    else if( !rs.next() ){
                        Thread.sleep(3000);
                        return -1;
                    }
                }
            } 
            catch (SQLException ex) {
                //Logger.getLogger(login.class.getName()).log(Level.SEVERE, null, ex);
                LogManager.logEvent(UserManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught SQL Exception: " + ex.getMessage(), null, username);
            }
            catch (ClassNotFoundException e1) {
                // JDBC driver class not found, print error message to the console
                System.out.println(e1.toString());
                LogManager.logEvent(UserManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Class Not Found Exception: " + e1.getMessage(), null, username);
            }
            catch (Exception e3) {
                // other unexpected exception, print error message to the console
                System.out.println(e3.toString());
                LogManager.logEvent(UserManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Exception: " + e3.getMessage(), null, username);
            }
        
        return -1;
    }
    
    public static void logout(HttpServletRequest request) {
        
        HttpSession session = request.getSession();
        if(session!=null) {            
            session.invalidate();
            request.getSession(true);
        }
    }
    
    public static void closeDB(){
        try {
            con.close();
            rs.close();
        } 
        catch (SQLException ex) {
            //Logger.getLogger(ProductManager.class.getName()).log(Level.SEVERE, null, ex);
            LogManager.logEvent(UserManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught SQL Exception: " + ex.getMessage(), null, null);
        }
    }
}
